# encoding: utf-8  
'''
Created on 2018年6月15日
description:数据收集器
@author: zhujianhua
'''
import requests
import time
import json
import sys
sys.path.append('./../helper')
# import InfoMonConstants
# from ConnectionHelper import ConnectionHelper
from helper import InfoMonConstants
from helper.ConnectionHelper import ConnectionHelper
from threading import Thread, Condition

class Collector:
    #收集数据列表
    uploadData = []
    #时间戳
    ts = int(time.time())
    
    def run(self):
        self.collect()
        self.send()

    #innodb缓存命中率
    def getInnodbCacheRatio(self, conn):
        results = conn.execute("select phy_read.count,total_read.count,1-phy_read.count/total_read.count\
    from (select count from information_schema.INNODB_METRICS t \
      where t.name =  'buffer_pool_reads') phy_read,\
     (select count from information_schema.INNODB_METRICS t\
      where t.name =  'buffer_pool_read_requests') total_read")
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_read_phyRead",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=bufferHitRatio",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_read_totalRead",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=bufferHitRatio",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_read_hit_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": round(line[2], 5),
                "counterType": "GAUGE",
                "tags": "name=bufferHitRatio",
            })

    #innodb缓存池利用率
    def getInnodbBufferUsage(self, conn):
        results = conn.execute("select buffer_free.count,buffer_total.count,1-buffer_free.count/buffer_total.count\
    from (select count from information_schema.INNODB_METRICS t\
      where t.name =  'buffer_pool_pages_free') buffer_free,\
     (select count from information_schema.INNODB_METRICS t\
      where t.name =  'buffer_pool_pages_total') buffer_total")
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_page_free",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=buffer_usage_ratio",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_page_total",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=buffer_usage_ratio",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "innodb_buffer_page_usage_Ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "name=buffer_usage_ratio",
            })

    #平均响应时间avg_response_time
    def getAvgResponseTime(self, conn):
        results = conn.execute("SELECT schema_name, SUM(count_star) count, \
       ROUND((SUM(sum_timer_wait)/SUM(count_star))/1000000000) AS avg_ms \
       FROM performance_schema.events_statements_summary_by_digest\
       WHERE schema_name IS NOT NULL GROUP BY schema_name")
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": line[0]+"_response_count",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=response_time_ms",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": line[0] + "_response_time_ms",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "name=response_time_ms",
            })


    #每秒接受字节数bytes_received
    def getBytesReceived(self, conn):
        BytesReceivedSql = "show global status like 'Bytes_received'"
        old_result = conn.getValue(BytesReceivedSql)
        time.sleep(1)
        new_result = conn.getValue(BytesReceivedSql)

        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "bytesReceived",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=avg_bytesReceived",
        })


    #每秒发送的字节数bytes_sent
    def getBytesSend(self, conn):
        BytesSendSql = "show global status like 'Bytes_sent'"
        old_result = conn.getValue(BytesSendSql)
        time.sleep(1)
        new_result = conn.getValue(BytesSendSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "bytesSend",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=avg_bytesSend",
        })

    #平均每秒从文件中读取的次数
    def getInnodbDataReads(self, conn):
        InnodbDataRead = "show global status like 'Innodb_data_reads'"
        old_result = conn.getValue(InnodbDataRead)
        time.sleep(1)
        new_result = conn.getValue(InnodbDataRead)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_data_read_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_data_read_count",
        })

    #平均每秒从文件中写入的次数innodb_data_writes
    def getInnodbDataWrites(self, conn):
        InnodbDataWrite = "show global status like 'Innodb_data_writes'"
        old_result = conn.getValue(InnodbDataWrite)
        time.sleep(1)
        new_result = conn.getValue(InnodbDataWrite)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_data_write_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_data_write_count",
        })

    #innodb平均每秒进行fsync() 操作的次数Innodb_data_fsyncs
    def getInnodbDataFsyncs(self, conn):
        InnodbDataFsyncs = "show global status like 'Innodb_data_fsyncs'"
        old_result = conn.getValue(InnodbDataFsyncs)
        time.sleep(1)
        new_result = conn.getValue(InnodbDataFsyncs)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_data_fsyncs",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_data_fsyncs",
        })

    #每秒事务数tps
    def getTps(self, conn):
        tpsCommit = "show global status like 'Com_commit'"
        tpsRollback = "show global status like 'Com_rollback'"
        old_commitresult = conn.getValue(tpsCommit)
        old_rollbackresult = conn.getValue(tpsRollback)
        old_tps = old_commitresult + old_rollbackresult
        time.sleep(1)
        new_commitresult = conn.getValue(tpsCommit)
        new_rollbackresult = conn.getValue(tpsRollback)
        new_tps = new_commitresult + new_rollbackresult
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "tps_per_second",
            "timestamp": self.ts,
            "step": 60,
            "value": new_tps - old_tps,
            "counterType": "GAUGE",
            "tags": "name=tps_per_second",
        })

    #每秒查询qps
    def getQps(self, conn):
        qpsSql = "show global status like 'Questions'"
        old_result = conn.getValue(qpsSql)
        time.sleep(1)
        new_result = conn.getValue(qpsSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "qps_per_second",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=qps_per_second",
        })

    #平均每秒钟读取的数据量，单位为KB
    def getInnodbDataRead(self, conn):
        innodbDataReadSql = "show global status like 'Innodb_data_read'"
        old_result = conn.getValue(innodbDataReadSql)
        time.sleep(1)
        new_result = conn.getValue(innodbDataReadSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "data_read_per_second",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=data_read_per_second",
        })

    #平均每秒钟写入的数据量，单位为KB，innodb_data_written
    def getInnodbDataWritten(self, conn):
        innodbDataWriteenSql = "show global status like 'Innodb_data_written'"
        old_result = conn.getValue(innodbDataWriteenSql)
        time.sleep(1)
        new_result = conn.getValue(innodbDataWriteenSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "data_written_per_second",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=data_written_per_second",
        })

    #平均每秒向日志文件完成的fsync()写数量，innodb_os_log_fsyncs
    def getInnodbOsLogFsyncs(self, conn):
        innodbOsLogFsyncs = "show global status like 'Innodb_os_log_fsyncs'"
        old_result = conn.getValue(innodbOsLogFsyncs)
        time.sleep(1)
        new_result = conn.getValue(innodbOsLogFsyncs)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "innodb_log_fsyncs",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "",
        })

    #平均每秒写入日志文件的字节数，innodb_os_log_written
    def getInnodbOsLogWritten(self, conn):
        InnodbOsLogWrittenSql = "show global status like 'Innodb_os_log_written'"
        old_result = conn.getValue(InnodbOsLogWrittenSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbOsLogWrittenSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "innodb_os_log_written",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=innodb_os_log_written",
        })


    #平均每秒向日志文件的物理写次数, Innodb_log_writes
    def getInnodbLogWrites(self, conn):
        InnodbLogWritesSql = "show global status like 'Innodb_log_writes'"
        old_result = conn.getValue(InnodbLogWritesSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbLogWritesSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "innodb_log_writes",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=innodb_log_writes",
        })

    #平均每秒日志写请求数
    def getInnodbLogWriteRequests(self, conn):
        InnodbLogWriteRequestSql = "show global status like 'Innodb_log_write_requests'"
        old_result = conn.getValue(InnodbLogWriteRequestSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbLogWriteRequestSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_log_write_requests",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_log_write_requests",
        })

    #平均每秒从innodb表删除的行数
    def getInnodbRowDeleted(self, conn):
        InnodbRowDeletedSql = "show global status like 'Innodb_rows_deleted'"
        old_result = conn.getValue(InnodbRowDeletedSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbRowDeletedSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_rows_deleted",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_rows_deleted",
        })

    #平均每秒从innodb表插入的行数, Innodb_rows_inserted
    def getInnodbRowsInserted(self, conn):
        InnodbRowsInsertedSql = "show global status like 'Innodb_rows_inserted'"
        old_result = conn.getValue(InnodbRowsInsertedSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbRowsInsertedSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_rows_inserted",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_rows_inserted",
        })

    #平均每秒从innodb表读取的行数, Innodb_rows_read
    def getInnodbRowsRead(self, conn):
        InnodbRowsReadSql = "show global status like 'Innodb_rows_read'"
        old_result = conn.getValue(InnodbRowsReadSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbRowsReadSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_rows_read",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_rows_read",
        })

    #平均每秒从innodb表更新的行数, Innodb_rows_updated
    def getInnodbRowsUpdated(self, conn):
        InnodbRowsUpdatedSql = "show global status like 'Innodb_rows_updated'"
        old_result = conn.getValue(InnodbRowsUpdatedSql)
        time.sleep(1)
        new_result = conn.getValue(InnodbRowsUpdatedSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_rows_updated",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_rows_updated",
        })

    #MyISAM平均每秒钟从缓冲池中的读取次数, key_read_requests
    def getKeyReadRequests(self, conn):
        KeyReadRequestsSql = "show global status like 'key_read_requests'"
        old_result = conn.getValue(KeyReadRequestsSql)
        time.sleep(1)
        new_result = conn.getValue(KeyReadRequestsSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "myisam_read_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=myisam_read_count",
        })

    #MyISAM平均每秒钟从缓冲池中的写入次数, Key_write_requests
    def getKeyWriteRequests(self, conn):
        KeyWriteRequestsSql = "show global status like 'Key_write_requests'"
        old_result = conn.getValue(KeyWriteRequestsSql)
        time.sleep(1)
        new_result = conn.getValue(KeyWriteRequestsSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "myisam_write_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=myisam_write_count",
        })

    #MyISAM平均每秒钟从硬盘上读取的次数,key_reads
    def getKeyReads(self, conn):
        KeyReadSql = "show global status like 'key_reads'"
        old_result = conn.getValue(KeyReadSql)
        time.sleep(1)
        new_result = conn.getValue(KeyReadSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "myisam_disk_read_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=myisam_disk_read_count",
        })

    #MyISAM平均每秒钟从硬盘上写入的次数
    def getKeyWrites(self, conn):
        KeyWritesSql = "show global status like 'key_writes'"
        old_result = conn.getValue(KeyWritesSql)
        time.sleep(1)
        new_result = conn.getValue(KeyWritesSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "myisam_disk_write_count",
            "timestamp": self.ts,
            "step": 60,
            "value": new_result - old_result,
            "counterType": "GAUGE",
            "tags": "name=myisam_disk_write_count",
        })

    #innodb引擎的并发数， threads_running
    def getThreadsRunning(self, conn):
        ThreadsRuningSql = "show global status like 'threads_running'"
        result = conn.getValue(ThreadsRuningSql)
        self.uploadData.append({
            "endpoint": InfoMonConstants.ENDPOINT,
            "metric": "Innodb_Thread_Running",
            "timestamp": self.ts,
            "step": 60,
            "value": result,
            "counterType": "GAUGE",
            "tags": "name=Innodb_Thread_Running",
        })
            
    #数据收集
    def collect(self):
        connHelper = ConnectionHelper(InfoMonConstants.SID)

        # self.getInnodbCacheRatio(connHelper)
        # self.getInnodbBufferUsage(connHelper)
        # self.getAvgResponseTime(connHelper)
        # self.getBytesReceived(connHelper)
        # self.getBytesSend(connHelper)
        # self.getInnodbDataReads(connHelper)
        # self.getInnodbDataWrites(connHelper)
        # self.getInnodbDataFsyncs(connHelper)
        # self.getTps(connHelper)
        # self.getQps(connHelper)
        # self.getInnodbDataRead(connHelper)
        # self.getInnodbDataWritten(connHelper)
        # self.getInnodbOsLogFsyncs(connHelper)
        # self.getInnodbOsLogWritten(connHelper)
        # self.getInnodbLogWrites(connHelper)
        # self.getInnodbLogWriteRequests(connHelper)
        # self.getInnodbRowDeleted(connHelper)
        # self.getInnodbRowsInserted(connHelper)
        # self.getInnodbRowsRead(connHelper)
        # self.getInnodbRowsUpdated(connHelper)
        # self.getKeyReadRequests(connHelper)
        # self.getKeyWriteRequests(connHelper)
        # self.getKeyReads(connHelper)
        # self.getKeyWrites(connHelper)
        threads = []
        t1 = Thread(target=self.getInnodbCacheRatio, args=(connHelper,))
        t2 = Thread(target=self.getInnodbBufferUsage, args=(connHelper,))
        t3 = Thread(target=self.getAvgResponseTime, args=(connHelper,))
        t4 = Thread(target=self.getBytesReceived, args=(connHelper,))
        t5 = Thread(target=self.getBytesSend, args=(connHelper,))
        t6 = Thread(target=self.getInnodbDataReads, args=(connHelper,))
        t7 = Thread(target=self.getInnodbDataWrites, args=(connHelper,))
        t8 = Thread(target=self.getInnodbDataFsyncs, args=(connHelper,))
        t9 = Thread(target=self.getTps, args=(connHelper,))
        t10 = Thread(target=self.getQps, args=(connHelper,))
        t11 = Thread(target=self.getInnodbDataRead, args=(connHelper,))
        t12 = Thread(target=self.getInnodbDataWritten, args=(connHelper,))
        t13 = Thread(target=self.getInnodbOsLogFsyncs, args=(connHelper,))
        t14 = Thread(target=self.getInnodbOsLogWritten, args=(connHelper,))
        t15 = Thread(target=self.getInnodbLogWrites, args=(connHelper,))
        t16 = Thread(target=self.getInnodbLogWriteRequests, args=(connHelper,))
        t17 = Thread(target=self.getInnodbRowDeleted, args=(connHelper,))
        t18 = Thread(target=self.getInnodbRowsInserted, args=(connHelper,))
        t19 = Thread(target=self.getInnodbRowsRead, args=(connHelper,))
        t20 = Thread(target=self.getInnodbRowsUpdated, args=(connHelper,))
        t21 = Thread(target=self.getKeyReadRequests, args=(connHelper,))
        t22 = Thread(target=self.getKeyWriteRequests, args=(connHelper,))
        t23 = Thread(target=self.getKeyReads, args=(connHelper,))
        t24 = Thread(target=self.getKeyWrites, args=(connHelper,))
        t25 = Thread(target=self.getThreadsRunning, args=(connHelper,))
        threads.append(t1)
        threads.append(t2)
        threads.append(t3)
        threads.append(t4)
        threads.append(t5)
        threads.append(t6)
        threads.append(t7)
        threads.append(t8)
        threads.append(t9)
        threads.append(t10)
        threads.append(t11)
        threads.append(t12)
        threads.append(t13)
        threads.append(t14)
        threads.append(t15)
        threads.append(t16)
        threads.append(t17)
        threads.append(t18)
        threads.append(t19)
        threads.append(t20)
        threads.append(t21)
        threads.append(t22)
        threads.append(t23)
        threads.append(t24)
        threads.append(t25)

        for t in threads:
            # t.setDaemon(True)
            t.start()
        for t in threads:
            t.join()

        for line in self.uploadData:
            print(line)

        connHelper.conn.close()
    
    
  
    
    #发送数据到transfer
    def send(self):
        if 0 != len(self.uploadData):
            r = requests.post(InfoMonConstants.FALCON_CLINENT, data=json.dumps(self.uploadData))
            print(r.text)



